<audio title="43 _ 要不要使用分区表？" src="https://static001.geekbang.org/resource/audio/93/2d/933a010d2267de5eb3ffb0e52eea7f2d.mp3" controls="controls"></audio> 
<p>我经常被问到这样一个问题：分区表有什么问题，为什么公司规范不让使用分区表呢？今天，我们就来聊聊分区表的使用行为，然后再一起回答这个问题。</p><h1>分区表是什么？</h1><p>为了说明分区表的组织形式，我先创建一个表t：</p><pre><code>CREATE TABLE `t` (
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);
</code></pre><p><img src="https://static001.geekbang.org/resource/image/06/f5/06f041129783533de9c75580f9decdf5.png" alt=""></p><center><span class="reference">图1 表t的磁盘文件</span></center><p>我在表t中初始化插入了两行记录，按照定义的分区规则，这两行记录分别落在p_2018和p_2019这两个分区上。</p><p>可以看到，这个表包含了一个.frm文件和4个.ibd文件，每个分区对应一个.ibd文件。也就是说：</p><ul>
<li>对于引擎层来说，这是4个表；</li>
<li>对于Server层来说，这是1个表。</li>
</ul><p>你可能会觉得这两句都是废话。其实不然，这两句话非常重要，可以帮我们理解分区表的执行逻辑。</p><h1>分区表的引擎层行为</h1><p>我先给你举个在分区表加间隙锁的例子，目的是说明对于InnoDB来说，这是4个表。</p><p><img src="https://static001.geekbang.org/resource/image/d2/c7/d28d6ab873bd8337d88812d45b9266c7.png" alt=""></p><center><span class="reference">图2 分区表间隙锁示例</span></center><p>这里顺便复习一下，我在<a href="https://time.geekbang.org/column/article/75659">第21篇文章</a>和你介绍的间隙锁加锁规则。</p><p>我们初始化表t的时候，只插入了两行数据， ftime的值分别是，'2017-4-1' 和'2018-4-1' 。session A的select语句对索引ftime上这两个记录之间的间隙加了锁。如果是一个普通表的话，那么T1时刻，在表t的ftime索引上，间隙和加锁状态应该是图3这样的。</p><!-- [[[read_end]]] --><p><img src="https://static001.geekbang.org/resource/image/27/d2/273c9ca869f5b52621641d73eb6f72d2.jpg" alt=""></p><center><span class="reference">图3 普通表的加锁范围</span></center><p>也就是说，'2017-4-1' 和'2018-4-1' 这两个记录之间的间隙是会被锁住的。那么，sesion B的两条插入语句应该都要进入锁等待状态。</p><p>但是，从上面的实验效果可以看出，session B的第一个insert语句是可以执行成功的。这是因为，对于引擎来说，p_2018和p_2019是两个不同的表，也就是说2017-4-1的下一个记录并不是2018-4-1，而是p_2018分区的supremum。所以T1时刻，在表t的ftime索引上，间隙和加锁的状态其实是图4这样的：</p><p><img src="https://static001.geekbang.org/resource/image/92/5c/92f63aba0b24adefac7316c75463b95c.jpg" alt=""></p><center><span class="reference">图4 分区表t的加锁范围</span></center><p>由于分区表的规则，session A的select语句其实只操作了分区p_2018，因此加锁范围就是图4中深绿色的部分。</p><p>所以，session B要写入一行ftime是2018-2-1的时候是可以成功的，而要写入2017-12-1这个记录，就要等session A的间隙锁。</p><p>图5就是这时候的show engine innodb status的部分结果。</p><p><img src="https://static001.geekbang.org/resource/image/e3/0f/e3d83d9ba89de9a6f541c9a2f24a3b0f.png" alt=""></p><center><span class="reference">图5 session B被锁住信息</span></center><p>看完InnoDB引擎的例子，我们再来一个MyISAM分区表的例子。</p><p>我首先用alter table t engine=myisam，把表t改成MyISAM表；然后，我再用下面这个例子说明，对于MyISAM引擎来说，这是4个表。</p><p><img src="https://static001.geekbang.org/resource/image/94/76/941306d4a7193455dcf1cfebf7678876.png" alt=""></p><center><span class="reference">图6 用MyISAM表锁验证</span></center><p>在session A里面，我用sleep(100)将这条语句的执行时间设置为100秒。由于MyISAM引擎只支持表锁，所以这条update语句会锁住整个表t上的读。</p><p>但我们看到的结果是，session B的第一条查询语句是可以正常执行的，第二条语句才进入锁等待状态。</p><p>这正是因为MyISAM的表锁是在引擎层实现的，session A加的表锁，其实是锁在分区p_2018上。因此，只会堵住在这个分区上执行的查询，落到其他分区的查询是不受影响的。</p><p>看到这里，你可能会说，分区表看来还不错嘛，为什么不让用呢？我们使用分区表的一个重要原因就是单表过大。那么，如果不使用分区表的话，我们就是要使用手动分表的方式。</p><p>接下来，我们一起看看手动分表和分区表有什么区别。</p><p>比如，按照年份来划分，我们就分别创建普通表t_2017、t_2018、t_2019等等。手工分表的逻辑，也是找到需要更新的所有分表，然后依次执行更新。在性能上，这和分区表并没有实质的差别。</p><p>分区表和手工分表，一个是由server层来决定使用哪个分区，一个是由应用层代码来决定使用哪个分表。因此，从引擎层看，这两种方式也是没有差别的。</p><p>其实这两个方案的区别，主要是在server层上。从server层看，我们就不得不提到分区表一个被广为诟病的问题：打开表的行为。</p><h1>分区策略</h1><p>每当第一次访问一个分区表的时候，MySQL需要把所有的分区都访问一遍。<strong>一个典型的报错情况</strong>是这样的：如果一个分区表的分区很多，比如超过了1000个，而MySQL启动的时候，open_files_limit参数使用的是默认值1024，那么就会在访问这个表的时候，由于需要打开所有的文件，导致打开表文件的个数超过了上限而报错。</p><p>下图就是我创建的一个包含了很多分区的表t_myisam，执行一条插入语句后报错的情况。</p><p><img src="https://static001.geekbang.org/resource/image/ab/e7/abfa0054ec43d97fb18ba3c1c8829ae7.png" alt=""></p><center><span class="reference">图 7 insert 语句报错</span></center><p>可以看到，这条insert语句，明显只需要访问一个分区，但语句却无法执行。</p><p>这时，你一定从表名猜到了，这个表我用的是MyISAM引擎。是的，因为使用InnoDB引擎的话，并不会出现这个问题。</p><p>MyISAM分区表使用的分区策略，我们称为<strong>通用分区策略</strong>（generic partitioning），每次访问分区都由server层控制。通用分区策略，是MySQL一开始支持分区表的时候就存在的代码，在文件管理、表管理的实现上很粗糙，因此有比较严重的性能问题。</p><p>从MySQL 5.7.9开始，InnoDB引擎引入了<strong>本地分区策略</strong>（native partitioning）。这个策略是在InnoDB内部自己管理打开分区的行为。</p><p>MySQL从5.7.17开始，将MyISAM分区表标记为即将弃用(deprecated)，意思是“从这个版本开始不建议这么使用，请使用替代方案。在将来的版本中会废弃这个功能”。</p><p>从MySQL 8.0版本开始，就不允许创建MyISAM分区表了，只允许创建已经实现了本地分区策略的引擎。目前来看，只有InnoDB和NDB这两个引擎支持了本地分区策略。</p><p>接下来，我们再看一下分区表在server层的行为。</p><h1>分区表的server层行为</h1><p>如果从server层看的话，一个分区表就只是一个表。</p><p>这句话是什么意思呢？接下来，我就用下面这个例子来和你说明。如图8和图9所示，分别是这个例子的操作序列和执行结果图。</p><p><img src="https://static001.geekbang.org/resource/image/0e/81/0eca5a3190161e59ea58493915bd5e81.png" alt=""></p><center><span class="reference">图8 分区表的MDL锁</span></center><p><img src="https://static001.geekbang.org/resource/image/af/a8/afe662f5e051a2ceb96a87624a589aa8.png" alt=""></p><center><span class="reference">图9 show processlist结果</span></center><p>可以看到，虽然session B只需要操作p_2017这个分区，但是由于session A持有整个表t的MDL锁，就导致了session B的alter语句被堵住。</p><p>这也是DBA同学经常说的，分区表，在做DDL的时候，影响会更大。如果你使用的是普通分表，那么当你在truncate一个分表的时候，肯定不会跟另外一个分表上的查询语句，出现MDL锁冲突。</p><p>到这里我们小结一下：</p><ol>
<li>
<p>MySQL在第一次打开分区表的时候，需要访问所有的分区；</p>
</li>
<li>
<p>在server层，认为这是同一张表，因此所有分区共用同一个MDL锁；</p>
</li>
<li>
<p>在引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必要的分区。</p>
</li>
</ol><p>而关于“必要的分区”的判断，就是根据SQL语句中的where条件，结合分区规则来实现的。比如我们上面的例子中，where ftime='2018-4-1'，根据分区规则year函数算出来的值是2018，那么就会落在p_2019这个分区。</p><p>但是，如果这个where 条件改成 where ftime&gt;='2018-4-1'，虽然查询结果相同，但是这时候根据where条件，就要访问p_2019和p_others这两个分区。</p><p>如果查询语句的where条件中没有分区key，那就只能访问所有分区了。当然，这并不是分区表的问题。即使是使用业务分表的方式，where条件中没有使用分表的key，也必须访问所有的分表。</p><p>我们已经理解了分区表的概念，那么什么场景下适合使用分区表呢？</p><h1>分区表的应用场景</h1><p>分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁。还有，分区表可以很方便的清理历史数据。</p><p>如果一项业务跑的时间足够长，往往就会有根据时间删除历史数据的需求。这时候，按照时间分区的分区表，就可以直接通过alter table t drop partition ...这个语法删掉分区，从而删掉过期的历史数据。</p><p>这个alter table t drop partition ...操作是直接删除分区文件，效果跟drop普通表类似。与使用delete语句删除数据相比，优势是速度快、对系统影响小。</p><h1>小结</h1><p>这篇文章，我主要和你介绍的是server层和引擎层对分区表的处理方式。我希望通过这些介绍，你能够对是否选择使用分区表，有更清晰的想法。</p><p>需要注意的是，我是以范围分区（range）为例和你介绍的。实际上，MySQL还支持hash分区、list分区等分区方法。你可以在需要用到的时候，再翻翻<a href="https://dev.mysql.com/doc/refman/8.0/en/partitioning-types.html">手册</a>。</p><p>实际使用时，分区表跟用户分表比起来，有两个绕不开的问题：一个是第一次访问的时候需要访问所有分区，另一个是共用MDL锁。</p><p>因此，如果要使用分区表，就不要创建太多的分区。我见过一个用户做了按天分区策略，然后预先创建了10年的分区。这种情况下，访问分区表的性能自然是不好的。这里有两个问题需要注意：</p><ol>
<li>
<p>分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。</p>
</li>
<li>
<p>分区也不要提前预留太多，在使用之前预先创建即可。比如，如果是按月分区，每年年底时再把下一年度的12个新分区创建上即可。对于没有数据的历史分区，要及时的drop掉。</p>
</li>
</ol><p>至于分区表的其他问题，比如查询需要跨多个分区取数据，查询性能就会比较慢，基本上就不是分区表本身的问题，而是数据量的问题或者说是使用方式的问题了。</p><p>当然，如果你的团队已经维护了成熟的分库分表中间件，用业务分表，对业务开发同学没有额外的复杂性，对DBA也更直观，自然是更好的。</p><p>最后，我给你留下一个思考题吧。</p><p>我们举例的表中没有用到自增主键，假设现在要创建一个自增字段id。MySQL要求分区表中的主键必须包含分区字段。如果要在表t的基础上做修改，你会怎么定义这个表的主键呢？为什么这么定义呢？</p><p>你可以把你的结论和分析写在留言区，我会在下一篇文章的末尾和你讨论这个问题。感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。</p><h1>上期问题时间</h1><p>上篇文章后面还不够多，可能很多同学还没来记得看吧，我们就等后续有更多留言的时候，再补充本期的“上期问题时间”吧。</p><p>@夹心面包 提到了在grant的时候是支持通配符的："_"表示一个任意字符，“%”表示任意字符串。这个技巧在一个分库分表方案里面，同一个分库上有多个db的时候，是挺方便的。不过我个人认为，权限赋值的时候，控制的精确性还是要优先考虑的。</p>
<style>
    ul {
      list-style: none;
      display: block;
      list-style-type: disc;
      margin-block-start: 1em;
      margin-block-end: 1em;
      margin-inline-start: 0px;
      margin-inline-end: 0px;
      padding-inline-start: 40px;
    }
    li {
      display: list-item;
      text-align: -webkit-match-parent;
    }
    ._2sjJGcOH_0 {
      list-style-position: inside;
      width: 100%;
      display: -webkit-box;
      display: -ms-flexbox;
      display: flex;
      -webkit-box-orient: horizontal;
      -webkit-box-direction: normal;
      -ms-flex-direction: row;
      flex-direction: row;
      margin-top: 26px;
      border-bottom: 1px solid rgba(233,233,233,0.6);
    }
    ._2sjJGcOH_0 ._3FLYR4bF_0 {
      width: 34px;
      height: 34px;
      -ms-flex-negative: 0;
      flex-shrink: 0;
      border-radius: 50%;
    }
    ._2sjJGcOH_0 ._36ChpWj4_0 {
      margin-left: 0.5rem;
      -webkit-box-flex: 1;
      -ms-flex-positive: 1;
      flex-grow: 1;
      padding-bottom: 20px;
    }
    ._2sjJGcOH_0 ._36ChpWj4_0 ._2zFoi7sd_0 {
      font-size: 16px;
      color: #3d464d;
      font-weight: 500;
      -webkit-font-smoothing: antialiased;
      line-height: 34px;
    }
    ._2sjJGcOH_0 ._36ChpWj4_0 ._2_QraFYR_0 {
      margin-top: 12px;
      color: #505050;
      -webkit-font-smoothing: antialiased;
      font-size: 14px;
      font-weight: 400;
      white-space: normal;
      word-break: break-all;
      line-height: 24px;
    }
    ._2sjJGcOH_0 ._10o3OAxT_0 {
      margin-top: 18px;
      border-radius: 4px;
      background-color: #f6f7fb;
    }
    ._2sjJGcOH_0 ._3klNVc4Z_0 {
      display: -webkit-box;
      display: -ms-flexbox;
      display: flex;
      -webkit-box-orient: horizontal;
      -webkit-box-direction: normal;
      -ms-flex-direction: row;
      flex-direction: row;
      -webkit-box-pack: justify;
      -ms-flex-pack: justify;
      justify-content: space-between;
      -webkit-box-align: center;
      -ms-flex-align: center;
      align-items: center;
      margin-top: 15px;
    }
    ._2sjJGcOH_0 ._10o3OAxT_0 ._3KxQPN3V_0 {
      color: #505050;
      -webkit-font-smoothing: antialiased;
      font-size: 14px;
      font-weight: 400;
      white-space: normal;
      word-break: break-word;
      padding: 20px 20px 20px 24px;
    }
    ._2sjJGcOH_0 ._3klNVc4Z_0 {
      display: -webkit-box;
      display: -ms-flexbox;
      display: flex;
      -webkit-box-orient: horizontal;
      -webkit-box-direction: normal;
      -ms-flex-direction: row;
      flex-direction: row;
      -webkit-box-pack: justify;
      -ms-flex-pack: justify;
      justify-content: space-between;
      -webkit-box-align: center;
      -ms-flex-align: center;
      align-items: center;
      margin-top: 15px;
    }
    ._2sjJGcOH_0 ._3Hkula0k_0 {
      color: #b2b2b2;
      font-size: 14px;
    }
</style><ul><li>
<div class="_2sjJGcOH_0"><img src="http://thirdwx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTJCscgdVibmoPyRLRaicvk6rjTJxePZ6VFHvGjUQvtfhCS6kO4OZ1AVibbhNGKlWZmpEFf2yA6ptsqHw/132"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>夹心面包</span>
  </div>
  <div class="_2_QraFYR_0">我说下我的感想<br>1 经典的利用分区表的场景<br>    1 zabbix历史数据表的改造,利用存储过程创建和改造<br>    2 后台数据的分析汇总,比如日志数据,便于清理<br>这两种场景我们都在执行,我们对于分区表在业务采用的是hash 用户ID方式,不过大规模应用分区表的公司我还没遇到过<br>2 分区表需要注意的几点<br>总结下<br>1 由于分区表都很大,DDL耗时是非常严重的,必须考虑这个问题<br>2 分区表不能建立太多的分区,我曾被分享一个因为分区表分区过多导致的主从延迟问题<br>3 分区表的规则和分区需要预先设置好,否则后来进行修改也很麻烦<br></div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 👍  非常好<br></p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-20 14:42:04</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/11/8a/3e/30c05bce.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>aliang</span>
  </div>
  <div class="_2_QraFYR_0">老师，mysql还有一个参数是innodb_open_files，资料上说作用是限制Innodb能打开的表的数量。它和open_files_limit之间有什么关系吗？</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 好问题。<br><br>在InnoDB引擎打开文件超过 innodb_open_files这个值的时候，就会关掉一些之前打开的文件。<br><br>其实我们文章中 ，InnoDB分区表使用了本地分区策略以后，即使分区个数大于open_files_limit ，打开InnoDB分区表也不会报“打开文件过多”这个错误，就是innodb_open_files这个参数发挥的作用。<br></p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-21 14:47:17</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/0f/67/f4/9a1feb59.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>钱</span>
  </div>
  <div class="_2_QraFYR_0">1：啥是分区表？<br>创建表的时候，使用了数据分区相关的语法，存储数据的时候，存储引擎会根据分区规则将不同的数据存入不同的分区文件。<br>2：使用分区表的劣势？<br>2-1：MySQL 在第一次打开分区表的时候，需要访问所有的分区——打开的表较多，性能糟糕也可能报打开的表超过设置的问题。<br>2-2：在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁——锁粒度大，影响并发度，站在Server看也是合理的，不过站在存储引擎的角度看就不合理了。<br>2-3：在引擎层，认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问必要的分区——被访问到的分区。<br>3：使用分区表的优势？<br>分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁。还有，分区表可以很方便的清理历史数据。<br>4：啥时候适合使用分区表？<br>单表过大时，使用时注意一下两点<br>4-1：分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。<br>4-2：分区也不要提前预留太多，在使用之前预先创建即可。比如，如果是按月分区，每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区，要及时的 drop 掉。<br>5：使用分区表，有其特点的根本原因？<br>对于Server层，分区表还是一个表<br>对于存储引擎层，分区表会是多张表</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-08-09 09:17:35</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/13/f9/a4/f0b92135.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>万勇</span>
  </div>
  <div class="_2_QraFYR_0">老师，请问add column after column_name跟add column不指定位置，这两种性能上有区别吗？我们在add column 指定after column_name的情况很多。</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 仅仅看性能，是没什么差别的<br><br>但是建议尽量不要加after column_name，<br>也就是说尽量加到最后一列。<br><br>因为其实没差别，但是加在最后有以下两个好处：<br>1. 开始有一些分支支持快速加列，就是说如果你加在最后一列，是瞬间就能完成，而加了after column_name，就用不上这些优化（以后潜在的好处）<br><br>2. 我们在前面的文章有提到过，如果怕对线上业务造成影响，有时候是通过“先做备库、切换、再做备库”这种方式来执行ddl的，那么使用after column_name的时候用不上这种方式。<br><br>实际上列的数据是不应该有影响的，还是要形成好习惯😆</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-21 17:41:47</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/13/e1/09/9483f537.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>☞</span>
  </div>
  <div class="_2_QraFYR_0">老师确认下，5.7.9之后的innodb分区表，是访问第一个表时不会去打开所有的分区表了吗？</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 第一次访问的时候，要打开所有分区的<br><br></p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-25 16:35:20</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/14/0c/ca/6173350b.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>郭江伟</span>
  </div>
  <div class="_2_QraFYR_0">此时主键包含自增列+分区键，原因为对innodb来说分区等于单独的表，自增字段每个分区可以插入相同的值，如果主键只有自增列无法完全保证唯一性。<br>测试表如下：<br>mysql&gt; show create table t\G<br>       Table: t<br>Create Table: CREATE TABLE `t` (<br>  `id` int(11) NOT NULL AUTO_INCREMENT,<br>  `ftime` datetime NOT NULL,<br>  `c` int(11) DEFAULT NULL,<br>  PRIMARY KEY (`id`,`ftime`),<br>  KEY `ftime` (`ftime`)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4<br>&#47;*!50100 PARTITION BY RANGE (YEAR(ftime))<br>(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,<br> PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,<br> PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,<br> PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB) *&#47;<br>1 row in set (0.00 sec)<br>mysql&gt; insert into t values(1,&#39;2017-4-1&#39;,1),(1,&#39;2018-4-1&#39;,1);<br>Query OK, 2 rows affected (0.02 sec)<br>mysql&gt; select * from t;<br>+----+---------------------+------+<br>| id | ftime               | c    |<br>+----+---------------------+------+<br>|  1 | 2017-04-01 00:00:00 |    1 |<br>|  1 | 2018-04-01 00:00:00 |    1 |<br>+----+---------------------+------+<br>2 rows in set (0.00 sec)<br><br>mysql&gt; insert into t values(null,&#39;2017-5-1&#39;,1),(null,&#39;2018-5-1&#39;,1);<br>Query OK, 2 rows affected (0.02 sec)<br><br>mysql&gt; select * from t;<br>+----+---------------------+------+<br>| id | ftime               | c    |<br>+----+---------------------+------+<br>|  1 | 2017-04-01 00:00:00 |    1 |<br>|  2 | 2017-05-01 00:00:00 |    1 |<br>|  1 | 2018-04-01 00:00:00 |    1 |<br>|  3 | 2018-05-01 00:00:00 |    1 |<br>+----+---------------------+------+<br>4 rows in set (0.00 sec)</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 👍</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-20 09:36:55</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/13/ef/af/ba786593.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>wljs</span>
  </div>
  <div class="_2_QraFYR_0"><br>老师我想问个问题 我们公司一个订单表有110个字段 想拆分成两个表 第一个表放经常查的字段 第二个表放不常查的 现在程序端不想改sql，数据库端来实现 当查询字段中 第一个表不存在 就去关联第二个表查出数据  db能实现不</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 用view可能可以实现部分你的需求，但是强烈不建议这么做。<br>业务不想修改，就好好跟他们说，毕竟这样分（常查和不常查的垂直拆分）是合理的，对读写性能都有明显的提升的。</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-20 08:48:51</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/10/a4/3b/a29c8eec.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>权恒星</span>
  </div>
  <div class="_2_QraFYR_0">这个只适合单机吧？集群没法即使用innodb引擎，又支持分区表吧，只能使用中间件了。之前调研了一下，官方只有ndb cluster才支持分区表？</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 对这篇文章讲的是单机上的单表多分区</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-20 20:34:51</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/13/60/71/895ee6cf.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>分清云淡</span>
  </div>
  <div class="_2_QraFYR_0">阿里云的DRDS就是分库分表的中间件典型代表。自己实现了一个层Server访问层在这一层进行分库分表（对透明），然后MySQL只是相当于存储层。一些Join、负载Order by&#47;Group by都在DRDS中间件这层完成，简单的逻辑插叙计算完对应的分库分表后下推给MySQL https:&#47;&#47;www.aliyun.com&#47;product&#47;drds</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-25 17:01:18</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/13/9f/6d/0a21fa84.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>Q</span>
  </div>
  <div class="_2_QraFYR_0">老师 请问下  网站开发数据库表是myisam和innodb混合引擎  考虑管理比较麻烦  想统一成innodb 请问是否影响数据库或带来什么隐患吗？  网站是网上商城购物类型的</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 应该统一成innodb<br>网上商城购物类型更要用InnoDB，因为MyISAM并不是crash-safe的。<br><br>测试环境改完回归下</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-20 21:25:25</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="http://thirdwx.qlogo.cn/mmopen/vi_32/fJ5BEicRVnXAwCxkIYhJZ0woiaQ38ibUYkYH125bzL2Y2ib1YS9b7Q9S5qia2Cia9UWzUoDBGeWJibB7p9xSnib7iaU8kzw/132"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>千木</span>
  </div>
  <div class="_2_QraFYR_0">老师您好，你在文章里面有说通用分区规则会打开所有引擎文件导致不可用，而本地分区规则应该是只打开单个引擎文件，那你不建议创建太多分区的原因是什么呢？如果是本地分区规则，照例说是不会影响的吧，叨扰了</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: “本地分区规则应该是只打开单个引擎文件”，并不是哈，我在文章末尾说了，也会打开所有文件的，只是说本地分区规则有优化，比如如果文件数过多，就会淘汰之前打开的文件句柄（暂时关掉）。<br><br>所以分区太多，还是会有影响的<br></p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-20 09:46:22</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/0f/74/67/46bb05de.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>怀刚</span>
  </div>
  <div class="_2_QraFYR_0">请教下采用”先做备库、切换、再做备库”DDL方式不支持AFTER COLUMN是因为BINLOG原因吗？<br>以上DDL方式会存在影响“有损”的吧？“无损”有哪些方案呢？如果备库承载读请求但又不能接受“长时间”延时</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 1. 对，binlog对原因<br>2. 如果延迟算损失，确实是有损的。备库上的读流量要先切换到主库（也就是为什么需要在低峰期做做个操作）</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-03-09 10:01:56</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/13/2b/58/11c05ccb.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>布衣骇客</span>
  </div>
  <div class="_2_QraFYR_0">这次竟然只需要再读两次就能读懂，之前接触过mycat和sharding-jdbc实现分区,老师能否谈谈这方面的呢</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 赞两次 😆<br><br>这个就是我们文章说的“分库分表中间件”<br>不过看到不少公司都会要在这基础上做点定制化</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-20 15:51:23</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/14/2f/6f/31721039.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>NICK</span>
  </div>
  <div class="_2_QraFYR_0">老师，如果用户分区，业务要做分页过滤查询怎么做才好？</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 分区表的用法跟普通表，在sql语句上是相同的。</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-25 15:00:00</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://wx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTJ8ibLXicovSNwEJhP4BOIPxEMle1ZUq1He58xztgBXTQFicdp5m5ibRHwUSoUVk5xQMWFwFXhp56ibm1Q/132"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>daka</span>
  </div>
  <div class="_2_QraFYR_0">本期提到了ndb，了解了下，这个存储引擎高可用及读写可扩展性功能都是自带，感觉是不错，为什么很少见人使用呢？生产不可靠？</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-21 22:55:06</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="http://thirdwx.qlogo.cn/mmopen/vi_32/MSV5CclX2Zct7U0F7bVwd0Zg4y6AK1qf8GVic5W3tCNaLhL6wTqD7CUnWxarW4DiaVbVic1G3gpZ3ud0ELWhuxnrg/132"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>helloworld.xs</span>
  </div>
  <div class="_2_QraFYR_0">请教个问题，一般mysql会有查询缓存，但是update操作也有缓存机制吗？使用mysql console第一次执行一个update SQL耗时明显比后面执行相同update SQL要慢，这是为什么？</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: update的话，主要应该第一次执行的时候，数据都读入到了</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-21 18:37:35</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/12/84/37/a13d867a.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>zc</span>
  </div>
  <div class="_2_QraFYR_0">alter table t drop partition 不加元数据锁吗？</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 要加 不过因为时间很短，可以认为是安全操作</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2020-02-10 16:19:14</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/10/4f/78/c3d8ecb0.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>undifined</span>
  </div>
  <div class="_2_QraFYR_0">老师，有两个问题<br>1. 图三的间隙锁，根据“索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁”，不应该是 (-∞,2017-4-1],(2017-4-1,2018-4-1)吗，图4左边的也应该是 (-∞,2017-4-1],(2017-4-1, supernum)，是不是图画错了<br>2. 现有的一个表，一千万行的数据， InnoDB 引擎，如果以月份分区，即使有 MDL 锁和初次访问时会查询所有分区，但是综合来看，分区表的查询性能还是要比不分区好，这样理解对吗<br><br>思考题的答案 <br>ALTER TABLE t<br>  ADD COLUMN (id INT AUTO_INCREMENT ),<br>  ADD PRIMARY KEY (id, ftime);<br><br>麻烦老师解答一下，谢谢老师</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 1. 我们语句里面是 where ftime=&#39;2017-5-1&#39; 哈，不是“4-1”<br>2. “分区表的查询性能还是要比不分区好，这样理解对吗”，其实还是要看表的索引情况。<br>   当然一定存在一个数量级N，把这N行分到10个分区表，比把这N行放到一个大表里面，效率高</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-20 10:53:49</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="http://thirdwx.qlogo.cn/mmopen/vi_32/ach5TNuIu8T4Of8ibvfWM4JPic5uQn9Y7TgGjBHLp2iar4icxJzs14bpUXV9OdYiciblvJl14zSqXjwtDffHnSTqbD6g/132"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>锋芒</span>
  </div>
  <div class="_2_QraFYR_0">老师，请问什么情况会出现间隙锁？能否专题讲一下锁呢？</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 20、21两篇看下</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2019-02-23 12:03:38</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://thirdwx.qlogo.cn/mmopen/vi_32/NP6xd0gtGwNWnzrk2hn1zgscu7ZuLRibDCrbJzHBRk1Bicb8X7uXlrfTH1Bh4epCNxts4ok4z1sGPHpWjtSaUqnQ/132"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>何霖</span>
  </div>
  <div class="_2_QraFYR_0">尊敬的作者，你说“分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了”，怎么判断索引是大索引还是小索引啊。</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2021-05-19 09:40:15</div>
  </div>
</div>
</div>
</li>
</ul>